﻿using FluentMigrator;

namespace RockBus.Migrations.Version1_0
{
    [Migration(1)]
    public class M0001CreateSubscriptionTables : Migration
    {
        public const string CT_DateTime2 = "DateTime2(7)";

        public override void Up()
        {
            CreateEnumTables();
            CreateSubscriptionTables();
            CreateForeignKeys();
        }

        public override void Down()
        {
        }

        private void CreateEnumTables()
        {
            Create.Table("TransportTypeEnum")
                  .WithColumn("DbId").AsInt64().NotNullable()
                  .WithColumn("Name").AsString(50).NotNullable()
                  .WithColumn("Description").AsString(250).Nullable();
            Create.PrimaryKey("PK_TransportTypeEnum").OnTable("TransportTypeEnum").Column("DbId");

            Create.Table("TransactionScopeOptionEnum")
                  .WithColumn("DbId").AsInt64().NotNullable()
                  .WithColumn("Name").AsString(50).NotNullable()
                  .WithColumn("Description").AsString(250).Nullable();
            Create.PrimaryKey("PK_TransactionScopeOptionEnum").OnTable("TransactionScopeOptionEnum").Column("DbId");

            Create.Table("ExchangeTypeEnum")
                .WithColumn("DbId").AsInt64().NotNullable()
                .WithColumn("Name").AsString(250).NotNullable()
                .WithColumn("Description").AsString(250).Nullable();
            Create.PrimaryKey("PK_ExchangeType").OnTable("ExchangeTypeEnum").Column("DbId");

            Create.Table("EndpointNameEnum")
                .WithColumn("DbId").AsInt64().NotNullable()
                .WithColumn("Name").AsString(250).NotNullable()
                .WithColumn("Description").AsString(250).Nullable();
            Create.PrimaryKey("PK_EndpointNameEnum").OnTable("EndpointNameEnum").Column("DbId");

            Create.Table("DeliveryModeEnum")
                .WithColumn("DbId").AsInt64().NotNullable()
                .WithColumn("Name").AsString(50).NotNullable()
                .WithColumn("Description").AsString(250).Nullable();
            Create.PrimaryKey("PK_DeliveryModeEnum").OnTable("DeliveryModeEnum").Column("DbId");

            Create.Table("ClientTypeEnum")
                .WithColumn("DbId").AsInt64().NotNullable()
                .WithColumn("Name").AsString(50).NotNullable()
                .WithColumn("Description").AsString(250).Nullable();
            Create.PrimaryKey("PK_ClientTypeEnum").OnTable("ClientTypeEnum").Column("DbId");

            Create.Table("FilterTypeEnum")
                .WithColumn("DbId").AsInt64().NotNullable()
                .WithColumn("Name").AsString(50).NotNullable()
                .WithColumn("Description").AsString(250).Nullable();
            Create.PrimaryKey("PK_FilterType").OnTable("FilterTypeEnum").Column("DbId");

            Create.Table("TransformationTypeEnum")
                .WithColumn("DbId").AsInt64().NotNullable()
                .WithColumn("Name").AsString(50).NotNullable()
                .WithColumn("Description").AsString(250).Nullable();
            Create.PrimaryKey("PK_TransformationTypeEnum").OnTable("TransformationTypeEnum").Column("DbId");
        }

        private void CreateSubscriptionTables()
        {
            Create.Table("Topic")
                  .WithColumn("DbId").AsInt64().Identity().NotNullable()
                  .WithColumn("Name").AsString(50).NotNullable();
            Create.PrimaryKey("PK_Topic").OnTable("Topic").Column("DbId");
            Create.Index("UIX_Topic").OnTable("Topic").OnColumn("Name").Unique();

            Create.Table("ReceivePort_Response")
                  .WithColumn("ReceivePortDbId").AsInt64().NotNullable()
                  .WithColumn("ResponseDbId").AsInt64().NotNullable();
            Create.PrimaryKey("PK_ReceivePort_Response")
                  .OnTable("ReceivePort_Response")
                  .Columns(new[] { "ReceivePortDbId", "ResponseDbId" });

            Create.Table("Client")
                  .WithColumn("DbId").AsInt64().Identity().NotNullable()
                  .WithColumn("Id").AsString(250).NotNullable()
                  .WithColumn("ClientTypeDbId").AsInt64().NotNullable()
                  .WithColumn("ModificationDateTimeUtc").AsCustom(CT_DateTime2).NotNullable();
            Create.PrimaryKey("PK_Client").OnTable("Client").Column("DbId");

            Create.Table("Subscriber")
                .WithColumn("SubscriberDbId").AsInt64().NotNullable();
            Create.PrimaryKey("PK_Subscriber").OnTable("Subscriber").Column("SubscriberDbId");

            Create.Table("Publisher")
               .WithColumn("PublisherDbId").AsInt64().NotNullable();
            Create.PrimaryKey("PK_Publisher").OnTable("Publisher").Column("PublisherDbId");

            Create.Table("Endpoint")
                  .WithColumn("DbId").AsInt64().Identity().NotNullable()
                  .WithColumn("Id").AsString(250).NotNullable()
                  .WithColumn("ClientDbId").AsInt64().NotNullable()
                  .WithColumn("Address").AsString(250).NotNullable()
                  .WithColumn("EndpointNameDbId").AsInt64().NotNullable()
                  .WithColumn("CustomEndpointName").AsString(250).NotNullable()
                  .WithColumn("TransportTypeDbId").AsInt64().NotNullable()
                  .WithColumn("CustomTransportType").AsString(250).NotNullable()
                  .WithColumn("ExchangeTypeDbId").AsInt64().NotNullable()
                  .WithColumn("TransactionScopeOptionDbId").AsInt64().NotNullable()
                  .WithColumn("Synchronous").AsBoolean().NotNullable();
            Create.PrimaryKey("PK_Endpoint").OnTable("Endpoint").Column("DbId");
            Create.Index("UIX_Endpoint_Id").OnTable("Endpoint")
                .OnColumn("Id").Ascending()
                .OnColumn("ClientDbId").Unique();

            Create.Table("NamespaceDefinition")
                  .WithColumn("DbId").AsInt64().Identity().NotNullable()
                  .WithColumn("ClientDbId").AsInt64().NotNullable()
                  .WithColumn("Prefix").AsString(50).NotNullable()
                  .WithColumn("Namespace").AsString(250).NotNullable();
            Create.PrimaryKey("PK_NamespaceDefinition").OnTable("NamespaceDefinition").Column("DbId");
            Create.Index("UIX_NamespaceDefinition").OnTable("NamespaceDefinition")
                .OnColumn("Prefix").Ascending()
                .OnColumn("ClientDbId").Unique();

            Create.Table("Filter")
                  .WithColumn("DbId").AsInt64().Identity().NotNullable()
                  .WithColumn("Id").AsString(250).NotNullable()
                  .WithColumn("ClientDbId").AsInt64().NotNullable()
                  .WithColumn("FilterTypeDbId").AsInt64().NotNullable()
                  .WithColumn("FilterData").AsString(int.MaxValue).NotNullable()
                  .WithColumn("CustomTypeName").AsString(250).Nullable()
                  .WithColumn("Filter1DbId").AsInt64().Nullable()
                  .WithColumn("Filter2DbId").AsInt64().Nullable();
            Create.PrimaryKey("PK_Filter").OnTable("Filter").Column("DbId");
            Create.Index("UIX_Filter_id").OnTable("Filter")
                .OnColumn("ClientDbId").Ascending()
                .OnColumn("Id").Unique();

            Create.Table("Response")
                  .WithColumn("DbId").AsInt64().Identity().NotNullable()
                  .WithColumn("Id").AsString(250).NotNullable()
                  .WithColumn("PublisherDbId").AsInt64().NotNullable()
                  .WithColumn("ResponseAction").AsString(int.MaxValue).NotNullable()
                  .WithColumn("BufferSize").AsInt32().NotNullable()
                  .WithColumn("ResponseXml").AsString(int.MaxValue).NotNullable();
            Create.PrimaryKey("PK_Response").OnTable("Response").Column("DbId");
            Create.Index("UIX_Response_id").OnTable("Response")
                .OnColumn("PublisherDbId").Ascending()
                .OnColumn("Id").Unique();

            Create.Table("Publisher_Topic")
                  .WithColumn("PublisherDbId").AsInt64().NotNullable()
                  .WithColumn("TopicDbId").AsInt64().NotNullable();
            Create.PrimaryKey("PK_Publisher_Topic")
                  .OnTable("Publisher_Topic")
                  .Columns(new[] { "PublisherDbId", "TopicDbId" });

            Create.Table("ReceivePort")
                  .WithColumn("DbId").AsInt64().Identity().NotNullable()
                  .WithColumn("Id").AsString(250).NotNullable()
                  .WithColumn("PublisherDbId").AsInt64().NotNullable()
                  .WithColumn("Paused").AsBoolean().NotNullable()
                  .WithColumn("ExpiryDateTimeUtc").AsCustom(CT_DateTime2).NotNullable()
                  .WithColumn("GenerateDefaultResponse").AsBoolean().NotNullable()
                  .WithColumn("MessageBufferId").AsString(250).NotNullable();
            Create.PrimaryKey("PK_ReceivePort").OnTable("ReceivePort").Column("DbId");
            Create.Index("UIX_ReceivePort").OnTable("ReceivePort")
                .OnColumn("Id").Ascending()
                .OnColumn("PublisherDbId").Unique();

            Create.Table("Subscription")
                  .WithColumn("DbId").AsInt64().Identity().NotNullable()
                  .WithColumn("Id").AsString(250).NotNullable()
                  .WithColumn("Persistent").AsBoolean().NotNullable()
                  .WithColumn("SubscriberDbId").AsInt64().NotNullable()
                  .WithColumn("DeliveryModeDbId").AsInt64().NotNullable()
                  .WithColumn("Paused").AsString(int.MaxValue).NotNullable()
                  .WithColumn("ExpiryDateTimeUtc").AsCustom(CT_DateTime2).NotNullable()
                  .WithColumn("GroupId").AsString(250).NotNullable()
                  .WithColumn("MessageValidityDurationSecs").AsInt64().NotNullable()
                  .WithColumn("MaxDeliveryAttemptCount").AsInt32().NotNullable()
                  .WithColumn("DeliveryAttemptIntervalSecs").AsInt64().NotNullable();
            Create.PrimaryKey("PK_Subscription").OnTable("Subscription").Column("DbId");
            Create.Index("UIX_Subscription").OnTable("Subscription")
                .OnColumn("Id").Ascending()
                .OnColumn("SubscriberDbId").Unique();

            Create.Table("Subscription_Filter")
                  .WithColumn("SubscriptionDbId").AsInt64().NotNullable()
                  .WithColumn("FilterDbId").AsInt64().NotNullable();
            Create.PrimaryKey("PK_Subscription_Filter")
                  .OnTable("Subscription_Filter")
                  .Columns(new[] { "SubscriptionDbId", "FilterDbId" });

            Create.Table("Subscription_Endpoint")
                  .WithColumn("SubscriptionDbId").AsInt64().NotNullable()
                  .WithColumn("EndpointDbId").AsInt64().NotNullable();
            Create.PrimaryKey("PK_Subscription_Endpoint")
                  .OnTable("Subscription_Endpoint")
                  .Columns(new[] { "SubscriptionDbId", "EndpointDbId" });

            Create.Table("ReceivePort_Filter")
                 .WithColumn("ReceivePortDbId").AsInt64().NotNullable()
                 .WithColumn("FilterDbId").AsInt64().NotNullable();
            Create.PrimaryKey("PK_ReceivePort_Filter")
                  .OnTable("ReceivePort_Filter")
                  .Columns(new[] { "ReceivePortDbId", "FilterDbId" });

            Create.Table("ReceivePort_Topic")
                 .WithColumn("ReceivePortDbId").AsInt64().NotNullable()
                 .WithColumn("TopicDbId").AsInt64().NotNullable();
            Create.PrimaryKey("PK_ReceivePort_Topic")
                  .OnTable("ReceivePort_Topic")
                  .Columns(new[] { "ReceivePortDbId", "TopicDbId" });

            Create.Table("Transformation")
                  .WithColumn("DbId").AsInt64().Identity().NotNullable()
                  .WithColumn("Id").AsString(250).NotNullable()
                  .WithColumn("ClientDbId").AsInt64().NotNullable()
                  .WithColumn("TransformationTypeDbId").AsInt64().NotNullable()
                  .WithColumn("TransformationData").AsString(int.MaxValue).Nullable()
                  .WithColumn("CustomTypeName").AsString(250).Nullable()
                  .WithColumn("SoapAction").AsString(250).Nullable();
            Create.PrimaryKey("PK_Transformation").OnTable("Transformation").Column("DbId");

            Create.Table("Subscription_Transformation")
                 .WithColumn("SubscriptionDbId").AsInt64().NotNullable()
                 .WithColumn("TransformationDbId").AsInt64().NotNullable()
                 .WithColumn("TransformResponse").AsBoolean().NotNullable();
            Create.PrimaryKey("PK_Subscription_Transformation")
                  .OnTable("Subscription_Transformation")
                  .Columns(new[] { "SubscriptionDbId", "TransformationDbId" });

            Create.Table("ReceivePort_Transformation")
                 .WithColumn("ReceivePortDbId").AsInt64().NotNullable()
                 .WithColumn("TransformationDbId").AsInt64().NotNullable()
                 .WithColumn("TransformResponse").AsBoolean().NotNullable();
            Create.PrimaryKey("PK_ReceivePort_Transformation")
                  .OnTable("ReceivePort_Transformation")
                  .Columns(new[] { "ReceivePortDbId", "TransformationDbId" });
        }

        private void CreateForeignKeys()
        {
            Create.ForeignKey("FK_ReceivePort_Publisher")
                 .FromTable("ReceivePort").ForeignColumn("PublisherDbId")
                 .ToTable("Publisher").PrimaryColumn("PublisherDbId");

            Create.ForeignKey("FK_ReceivePort_Filter_ReceivePort")
                .FromTable("ReceivePort_Filter").ForeignColumn("ReceivePortDbId")
                .ToTable("ReceivePort").PrimaryColumn("DbId");

            Create.ForeignKey("FK_ReceivePort_Filter_Filter")
                .FromTable("ReceivePort_Filter").ForeignColumn("FilterDbId")
                .ToTable("Filter").PrimaryColumn("DbId");

            Create.ForeignKey("FK_ReceivePort_Topic_ReceivePort")
                .FromTable("ReceivePort_Topic").ForeignColumn("ReceivePortDbId")
                .ToTable("ReceivePort").PrimaryColumn("DbId");

            Create.ForeignKey("FK_ReceivePort_Topic_Topic")
                .FromTable("ReceivePort_Topic").ForeignColumn("TopicDbId")
                .ToTable("Topic").PrimaryColumn("DbId");

            Create.ForeignKey("FK_Client_ClientTypeEnum")
                .FromTable("Client").ForeignColumn("ClientTypeDbId")
                .ToTable("ClientTypeEnum").PrimaryColumn("DbId");

            Create.ForeignKey("FK_Endpoint_Client")
                .FromTable("Endpoint").ForeignColumn("ClientDbId")
                .ToTable("Client").PrimaryColumn("DbId");

            Create.ForeignKey("FK_Endpoint_EndpointNameEnum")
                .FromTable("Endpoint").ForeignColumn("EndpointNameDbId")
                .ToTable("EndpointNameEnum").PrimaryColumn("DbId");

            Create.ForeignKey("FK_Endpoint_ExchangeTypeEnum")
                .FromTable("Endpoint").ForeignColumn("ExchangeTypeDbId")
                .ToTable("ExchangeTypeEnum").PrimaryColumn("DbId");

            Create.ForeignKey("FK_Endpoint_TransactionScopeOptionEnum")
                .FromTable("Endpoint").ForeignColumn("TransactionScopeOptionDbId")
                .ToTable("TransactionScopeOptionEnum").PrimaryColumn("DbId");

            Create.ForeignKey("FK_Endpoint_TransportTypeEnum")
                .FromTable("Endpoint").ForeignColumn("TransportTypeDbId")
                .ToTable("TransportTypeEnum").PrimaryColumn("DbId");

            Create.ForeignKey("FK_Filter_Client")
                .FromTable("Filter").ForeignColumn("ClientDbId")
                .ToTable("Client").PrimaryColumn("DbId");

            Create.ForeignKey("FK_Filter_FilterTypeEnum")
               .FromTable("Filter").ForeignColumn("FilterTypeDbId")
               .ToTable("FilterTypeEnum").PrimaryColumn("DbId");

            Create.ForeignKey("FK_Filter1_Filter")
               .FromTable("Filter").ForeignColumn("Filter1DbId")
               .ToTable("Filter").PrimaryColumn("DbId");

            Create.ForeignKey("FK_Filter2_Filter")
               .FromTable("Filter").ForeignColumn("Filter2DbId")
               .ToTable("Filter").PrimaryColumn("DbId");

            Create.ForeignKey("FK_NamespaceDefinition_Client")
               .FromTable("NamespaceDefinition").ForeignColumn("ClientDbId")
               .ToTable("Client").PrimaryColumn("DbId");

            Create.ForeignKey("FK_Publisher_Client")
               .FromTable("Publisher").ForeignColumn("PublisherDbId")
               .ToTable("Client").PrimaryColumn("DbId");

            Create.ForeignKey("FK_Publisher_Topic_Publisher")
               .FromTable("Publisher_Topic").ForeignColumn("PublisherDbId")
               .ToTable("Publisher").PrimaryColumn("PublisherDbId");

            Create.ForeignKey("FK_Publisher_Topic_Topic")
               .FromTable("Publisher_Topic").ForeignColumn("TopicDbId")
               .ToTable("Topic").PrimaryColumn("DbId");

            Create.ForeignKey("FK_Response_Publisher")
               .FromTable("Response").ForeignColumn("PublisherDbId")
               .ToTable("Publisher").PrimaryColumn("PublisherDbId");

            Create.ForeignKey("FK_Subscriber_Client")
               .FromTable("Subscriber").ForeignColumn("SubscriberDbId")
               .ToTable("Client").PrimaryColumn("DbId");

            Create.ForeignKey("FK_Subscription_DeliveryModeEnum")
               .FromTable("Subscription").ForeignColumn("DeliveryModeDbId")
               .ToTable("DeliveryModeEnum").PrimaryColumn("DbId");

            Create.ForeignKey("FK_Subscription_Subscriber")
               .FromTable("Subscription").ForeignColumn("SubscriberDbId")
               .ToTable("Subscriber").PrimaryColumn("SubscriberDbId");

            Create.ForeignKey("FK_Subscription_Endpoint_Endpoint")
              .FromTable("Subscription_Endpoint").ForeignColumn("EndpointDbId")
              .ToTable("Endpoint").PrimaryColumn("DbId");

            Create.ForeignKey("FK_Subscription_Endpoint_Subscription")
             .FromTable("Subscription_Endpoint").ForeignColumn("SubscriptionDbId")
             .ToTable("Subscription").PrimaryColumn("DbId");

            Create.ForeignKey("FK_Subscription_Filter_Filter")
             .FromTable("Subscription_Filter").ForeignColumn("FilterDbId")
             .ToTable("Filter").PrimaryColumn("DbId");

            Create.ForeignKey("FK_Subscription_Filter_Subscription")
             .FromTable("Subscription_Filter").ForeignColumn("SubscriptionDbId")
             .ToTable("Subscription").PrimaryColumn("DbId");

            Create.ForeignKey("FK_Transformation_Client")
               .FromTable("Transformation").ForeignColumn("ClientDbId")
               .ToTable("Client").PrimaryColumn("DbId");

            Create.ForeignKey("FK_Transformation_TransfTypeEnum")
               .FromTable("Transformation").ForeignColumn("TransformationTypeDbId")
               .ToTable("TransformationTypeEnum").PrimaryColumn("DbId");

            Create.ForeignKey("FK_Subscription_Transformation_Transf")
               .FromTable("Subscription_Transformation").ForeignColumn("TransformationDbId")
               .ToTable("Transformation").PrimaryColumn("DbId");

            Create.ForeignKey("FK_Subscription_Transf_Subscription")
               .FromTable("Subscription_Transformation").ForeignColumn("SubscriptionDbId")
               .ToTable("Subscription").PrimaryColumn("DbId");

            Create.ForeignKey("FK_ReceivePort_Transf_ReceivePort")
               .FromTable("ReceivePort_Transformation").ForeignColumn("ReceivePortDbId")
               .ToTable("ReceivePort").PrimaryColumn("DbId");

            Create.ForeignKey("FK_ReceivePort_Transformation_Transf")
               .FromTable("ReceivePort_Transformation").ForeignColumn("TransformationDbId")
               .ToTable("Transformation").PrimaryColumn("DbId");
        }
    }
}